﻿<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>

<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Get : DataMapper ORM - User Guide</title>

<link rel="shortcut icon" type="image/png" href="../images/favicon.png" />
<link rel="stylesheet" type="text/css" media="all" href="../css/userguide.css" />
<link rel="alternate" type="application/rss+xml" title="Datamapper ORM Updates Feed" href="/rss.xml" />

<meta http-equiv="expires" content="-1" />
<meta http-equiv= 'pragma' content="no-cache" />
<meta name="robots" content="all" />

</head>

<body>

<!-- START NAVIGATION -->
<div id="nav"><div id="nav_inner"></div></div>
<div id="nav2"><a name="top">&nbsp;</a><a id="nav_toggle" href="#"><img src="../images/nav_toggle_darker.jpg" width="154" height="43" border="0" title="Toggle Table of Contents" alt="Toggle Table of Contents" /></a></div>
<div id="masthead">
<table cellpadding="0" cellspacing="0" border="0" style="width:100%">
<tr>
<td><h1>DataMapper ORM</h1></td>
<td id="breadcrumb_right"><a href="toc.html">Table of Contents Page</a></td>
</tr>
</table>
</div>
<!-- END NAVIGATION -->

<!-- START BREADCRUMB -->
<table cellpadding="0" cellspacing="0" border="0" style="width:100%">
<tr>
<td id="breadcrumb">
<a href="/">Datamapper ORM Home</a> &nbsp;&#8250;&nbsp;
<a href="../index.html">User Guide Home</a> &nbsp;&#8250;&nbsp;
Get
</td>
</tr>

</table>
<!-- END BREADCRUMB -->

<br clear="all" />


<!-- START CONTENT -->
<div id="content">


<h1 id="Get">Get</h1>

<p>You'll find Get is used in a very similar way to retrieving data with CodeIgniter's <a href="http://codeigniter.com/user_guide/database/active_record.html">Active Record</a> class.  All the relevant query clauses from Active Record are available in DataMapper so you have the full power of retrieving data, in Active Record style!</p>

<p class="important"><strong>Note:</strong> There are enough differences between CodeIgniter and DataMapper's Active Record like query clauses that you should read on to be able to take full advantage of it.</p>

<br />

<p>Now, let's look at all the available methods.  We'll assume we have a DataMapper model setup, named Object.</p>

<h2>Subsections</h2>
<ul>
	<li><a href="#Get">Basic Get</a> (This Section)</li>
	<li><a href="#Field.Selection">Field Selection</a></li>
	<li><a href="#Limiting.Results">Limiting Results</a></li>
	<li><a href="#Query.Grouping">Query Grouping</a></li>
	<li><a href="#Other.Features">Other Features</a></li>
	<li><a href="#Method.Chaining">Method Chaining</a></li>
	<li><a href="#Active.Record.Caching">Active Record Caching</a></li>
</ul>

<h2 id="get">$object->get();</h2>

<p>Runs the selection query and returns the result.  Can be used by itself to retrieve all records from a table:</p>

<pre>
<var>$o </var><kbd>= new </kbd><var>Object</var><kbd>();
</kbd><var>$o</var><kbd>-&gt;</kbd><var><u>get</u></var><kbd>();

</kbd><samp>// The $o object is populated with all objects from its corresponding table</samp>
</pre>

<p>The first and second parameters enable you do set a limit and offset clause:</p>

<pre>
<var>$o </var><kbd>= new </kbd><var>Object</var><kbd>();
</kbd><var>$o</var><kbd>-&gt;</kbd><var><u>get</u></var><kbd>(</kbd><var>10</var><kbd>, </kbd><var>20</var><kbd>);

</kbd><samp>// The $o object is populated with 10 objects from its corresponding table, starting from record 20</samp>
</pre>


<p>You can view the results in a couple of ways. Viewing the first result:</p>

<pre>
<var>$o </var><kbd>= new </kbd><var>Object</var><kbd>();
</kbd><var>$o</var><kbd>-&gt;</kbd><var><u>get</u></var><kbd>();

echo </kbd><var>$o</var><kbd>-&gt;</kbd><var>title</var><kbd>;</kbd>
</pre>

<p>Viewing all results:</p>

<pre>
<var>$o </var><kbd>= new </kbd><var>Object</var><kbd>();
</kbd><var>$o</var><kbd>-&gt;</kbd><var><u>get</u></var><kbd>();

foreach (</kbd><var>$o</var> <kbd>as </kbd><var>$obj</var><kbd>)
{
    echo </kbd><var>$obj</var><kbd>-&gt;</kbd><var>title</var><kbd>;
}</kbd>
</pre>

<p class="note">If you are looping over the results, and don't need direct access, you may see a performance improvement by using <a href="getalt.html#get_iterated">get_iterated</a>.</p>

<h2 id="validate.get">$object->validate->get();</h2>
<p>Normally, get() will generate its query from building up any query clauses you have setup before calling get().  If none are setup, it will default to selecting all records from the objects corresponding table.  However, there is a special situation where get() will use the values present within the current object.  This happens if you run the validate() function before a get() call.</p>
<p class="important"><strong>Note:</strong> When doing $object->validate()->get(); all other query clauses (such as select, where etc) will be ignored.</p>

<p>Ok, so to explain this, I'll use an example I've used before from the <a href="gettingstarted.html">Getting Started</a> page.  Taking part of the example from there, we see that the User model is setup to encrypt the password field with the salt from the matching users stored record (by username), when they attempt to login.</p>

<h3>User model (excerpt)</h3>
<pre class="full"><div class="lineNums">105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142</div>    <kbd>function </kbd><var>login</var><kbd>()
    {
        </kbd><samp>// backup username for invalid logins
        </samp><var>$uname </var><kbd>= </kbd><var><b>$this</b></var><kbd>-&gt;</kbd><var>username</var><kbd>;

        </kbd><samp>// Create a temporary user object
        </samp><var>$u </var><kbd>= new </kbd><var>User</var><kbd>();

        </kbd><samp>// Get this users stored record via their username
        </samp><var>$u</var><kbd>-&gt;</kbd><var><u>where</u></var><kbd>(</kbd><dfn>'username'</dfn><kbd>, </kbd><var>$uname</var><kbd>)-&gt;</kbd><var><u>get</u></var><kbd>();

        </kbd><samp>// Give this user their stored salt
        </samp><var><b>$this</b></var><kbd>-&gt;</kbd><var>salt </var><kbd>= </kbd><var>$u</var><kbd>-&gt;</kbd><var>salt</var><kbd>;

        </kbd><samp>// Validate and get this user by their property values,
        // this will see the 'encrypt' validation run, encrypting the password with the salt
        </samp><em class="new"><var><b>$this</b></var><kbd>-&gt;</kbd><var><u>validate</u></var><kbd>()-&gt;</kbd><var><u>get</u></var><kbd>();</em>

        </kbd><samp>// If the username and encrypted password matched a record in the database,
        // this user object would be fully populated, complete with their ID.

        // If there was no matching record, this user would be completely cleared so their id would be empty.
        </samp><kbd>if (</kbd><var><b>$this</b></var><kbd>-&gt;</kbd><var><u>exists</u></var><kbd>())
        {
            </kbd><samp>// Login succeeded
            </samp><kbd>return </kbd><var><dfn>TRUE</dfn></var><kbd>;
        }
        else
        {
            </kbd><samp>// Login failed, so set a custom error message
            </samp><var><b>$this</b></var><kbd>-&gt;</kbd><var><u>error_message</u></var><kbd>(</kbd><dfn>'login'</dfn><kbd>, </kbd><dfn>'Username or password invalid'</dfn><kbd>);

            </kbd><samp>// restore username for login field
            </samp><var><b>$this</b></var><kbd>-&gt;</kbd><var>username </var><kbd>= </kbd><var>$uname</var><kbd>;

            return </kbd><var><dfn>FALSE</dfn></var><kbd>;
        }
    }</kbd>
</pre>

<p>Here's how the models login function was called. You can see the username and unencrypted password is set on the user object before calling the login function.</p>
<h3>Controller (excerpt)</h3>
<pre>
        <samp>// Create user object
        </samp><var>$u </var><kbd>= new </kbd><var>User</var><kbd>();

        </kbd><samp>// Put user supplied data into user object
        // (no need to validate the post variables in the controller,
        // if you've set your DataMapper models up with validation rules)
        </samp><var>$u</var><kbd>-&gt;</kbd><var>username </var><kbd>= </kbd><var><b>$this</b></var><kbd>-&gt;</kbd><var>input</var><kbd>-&gt;</kbd><var>post</var><kbd>(</kbd><dfn>'username'</dfn><kbd>);
        </kbd><var>$u</var><kbd>-&gt;</kbd><var>password </var><kbd>= </kbd><var><b>$this</b></var><kbd>-&gt;</kbd><var>input</var><kbd>-&gt;</kbd><var>post</var><kbd>(</kbd><dfn>'password'</dfn><kbd>);

        </kbd><samp>// Attempt to log user in with the data they supplied, using the login function setup in the User model
        // You might want to have a quick look at that login function up the top of this page to see how it authenticates the user
        </samp><kbd>if (</kbd><var>$u</var><kbd>-&gt;</kbd><var>login</var><kbd>())
        {
                echo </kbd><dfn>'&lt;p&gt;Welcome ' </dfn><kbd>. </kbd><var><b>$this</b></var><kbd>-&gt;</kbd><var>username </var><kbd>. </kbd><dfn>'!&lt;/p&gt;'</dfn><kbd>;
                echo </kbd><dfn>'&lt;p&gt;You have successfully logged in so now we know that your email is ' </dfn><kbd>. </kbd><var><b>$this</b></var><kbd>-&gt;</kbd><var>email </var><kbd>. </kbd><dfn>'.&lt;/p&gt;'</dfn><kbd>;
        }
        else
        {
                </kbd><samp>// Show the custom login error message
                </samp><kbd>echo </kbd><dfn>'&lt;p&gt;' </dfn><kbd>. </kbd><var><b>$this</b></var><kbd>-&gt;</kbd><var><i>error</i></var><kbd>-&gt;</kbd><var>login </var><kbd>. </kbd><dfn>'&lt;/p&gt;'</dfn><kbd>;
        }</kbd>
</pre>

<p>So, inside, the models login function, $object->validate->get(); is called which runs the validation functions, defined in the model, on the objects properties, and then it does a get using the validated properties.</p>


<h2 id="get_where">$object->get_where();</h2>

<p>Identical to the above function except that it permits you to add a "where" clause in the first parameter,
instead of using the $object->where() function:</p>

<pre>
<var>$o </var><kbd>= new </kbd><var>Object</var><kbd>();
</kbd><var>$o</var><kbd>-&gt;</kbd><var><u>get_where</u></var><kbd>(array(</kbd><dfn>'id'</dfn><kbd> =&gt; </kbd><var>$id</var><kbd>), </kbd><var>$limit</var><kbd>, </kbd><var>$offset</var><kbd>);</kbd>
</pre>

<p>Please read the <var><u>where</u></var> function below for more information.</p>





<p>&nbsp;</p>

<h1 id="Field.Selection">Field Selection</h1>
<p>Use the following methods to limit or change which fields are selected.</p>
<p>
	You can select values from related objects and join tables in the next section, <a href="getadvanced.html#Get.Advanced.Selection">Get (Advanced)</a>.
	There are also methods for selecting <a href="functions.html">SQL Functions</a> and <a href="subqueries.html">Subqueries</a>.
</p>

<h2 id="select">$object->select();</h2>

<p>Permits you to write the SELECT portion of your query:</p>
<pre>
<var>$o </var><kbd>= new </kbd><var>Object</var><kbd>();
</kbd><var>$o</var><kbd>-&gt;</kbd><var><u>select</u></var><kbd>(</kbd><dfn>'title, description'</dfn><kbd>);

</kbd><var>$o</var><kbd>-&gt;</kbd><var><u>get</u></var><kbd>();

</kbd><samp>// The $o object is populated with all objects from its corresponding table, but with only the title and description fields populated</samp>
</pre>

<p class="note"><strong>Note:</strong> If you are selecting all (*) from a table you do not need to use this function.  When omitted, DataMapper assumes you wish to SELECT *</p>

<h2 id="select_max">$object->select_max();</h2>
<p>Writes a "SELECT MAX(field)" portion for your query. You can optionally include a second parameter to rename the resulting field.</p>
<pre>
<var>$o </var><kbd>= new </kbd><var>Object</var><kbd>();
</kbd><var>$o</var><kbd>-&gt;</kbd><var><u>select_max</u></var><kbd>(</kbd><dfn>'age'</dfn><kbd>);
</kbd><var>$o</var><kbd>-&gt;</kbd><var><u>get</u></var><kbd>();

</kbd><samp>// The $o object is populated with a single object from its corresponding table, but with only the age field populated, which contains the maximum age</samp>
</pre>


<h2 id="select_min">$object->select_min();</h2>
<p>Writes a "SELECT MIN(field)" portion for your query. As with <var><u>select_max</u></var><kbd>()</kbd>, You can optionally include a second parameter to rename the resulting field.</p>
<pre>
<var>$o </var><kbd>= new </kbd><var>Object</var><kbd>();
</kbd><var>$o</var><kbd>-&gt;</kbd><var><u>select_min</u></var><kbd>(</kbd><dfn>'age'</dfn><kbd>);
</kbd><var>$o</var><kbd>-&gt;</kbd><var><u>get</u></var><kbd>();

</kbd><samp>// The $o object is populated with a signle object from its corresponding table, but with only the age field populated, which contains the minimum age</samp>
</pre>

<h2 id="select_avg">$object->select_avg();</h2>
<p>Writes a "SELECT AVG(field)" portion for your query. As with <var><u>select_max</u></var><kbd>()</kbd>, You can optionally include a second parameter to rename the resulting field.</p>

<pre>
<var>$o </var><kbd>= new </kbd><var>Object</var><kbd>();
</kbd><var>$o</var><kbd>-&gt;</kbd><var><u>select_avg</u></var><kbd>(</kbd><dfn>'age'</dfn><kbd>);
</kbd><var>$o</var><kbd>-&gt;</kbd><var><u>get</u></var><kbd>();

</kbd><samp>// The $o object is populated with a single object from its corresponding table, but with only the age field populated, which contains the average age</samp>
</pre>

<h2 id="select_sum">$object->select_sum();</h2>
<p>Writes a "SELECT SUM(field)" portion for your query. As with <var><u>select_max</u></var><kbd>()</kbd>, You can optionally include a second parameter to rename the resulting field.</p>
<pre>
<var>$o </var><kbd>= new </kbd><var>Object</var><kbd>();
</kbd><var>$o</var><kbd>-&gt;</kbd><var><u>select_sum</u></var><kbd>(</kbd><dfn>'age'</dfn><kbd>);
</kbd><var>$o</var><kbd>-&gt;</kbd><var><u>get</u></var><kbd>();

</kbd><samp>// The $o object is populated with a single object from its corresponding table, but with only the age field populated, which contains the sum of all ages</samp>
</pre>

<h2 id="distinct">$object->distinct();</h2>
<p>Adds the &quot;DISTINCT&quot; keyword to  a query</p>

<pre>
<var>$o </var><kbd>= new </kbd><var>Object</var><kbd>();
</kbd><var>$o</var><kbd>-&gt;</kbd><var><u>distinct</u></var><kbd>();

</kbd><samp>// When $o-&gt;get() is called, a DISTINCT select of records will be made</samp>
</pre>






<p>&nbsp;</p>

<h1 id="Limiting.Results">Limiting Results</h1>
<p>Use the following methods to limit or change which rows are returned.</p>
<p>
	You can query values from related objects and join tables in the next section, <a href="getadvanced.html#Get.Advanced.Selection">Get (Advanced)</a>.
	There are also methods for using <a href="functions.html">SQL Functions</a> and <a href="subqueries.html">Subqueries</a> in queries.
</p>

<h2 id="where">$object->where();</h2>
<p>This function enables you to set <strong>WHERE</strong> clauses using one of four methods:</p>

<p class="note"><strong>Note:</strong> All values passed to this function are escaped automatically, producing safer queries.</p>

<ol>
	<li><strong>Simple key/value method:</strong>
<pre>
<var>$o </var><kbd>= new </kbd><var>Object</var><kbd>();
</kbd><var>$o</var><kbd>-&gt;</kbd><var><u>where</u></var><kbd>(</kbd><dfn>'name'</dfn><kbd>, </kbd><var>$name</var><kbd>);
</kbd><samp>// When $o-&gt;get() is called, the above where clause will be included in the get query</samp></pre>

	<p>If you use multiple where function calls they will be chained together with <var>AND</var> between them:</p>
<pre>
<var>$o </var><kbd>= new </kbd><var>Object</var><kbd>();
</kbd><var>$o</var><kbd>-&gt;</kbd><var><u>where</u></var><kbd>(</kbd><dfn>'name'</dfn><kbd>, </kbd><var>$name</var><kbd>);
</kbd><var>$o</var><kbd>-&gt;</kbd><var><u>where</u></var><kbd>(</kbd><dfn>'title'</dfn><kbd>, </kbd><var>$title</var><kbd>);
</kbd><var>$o</var><kbd>-&gt;</kbd><var><u>where</u></var><kbd>(</kbd><dfn>'status'</dfn><kbd>, </kbd><var>$status</var><kbd>);
</kbd><samp>// When $o-&gt;get() is called, all of the above where clause will be included in the get query</samp>
</pre>

    </li>

	<li><strong>Custom key/value method:</strong>

	<p>You can include an operator in the first parameter in order to control the comparison:</p>
<pre>
<var>$o </var><kbd>= new </kbd><var>Object</var><kbd>();
</kbd><var>$o</var><kbd>-&gt;</kbd><var><u>where</u></var><kbd>(</kbd><dfn>'name !='</dfn><kbd>, </kbd><var>$name</var><kbd>);
</kbd><var>$o</var><kbd>-&gt;</kbd><var><u>where</u></var><kbd>(</kbd><dfn>'id &lt;'</dfn><kbd>, </kbd><var>$id</var><kbd>);
</kbd><samp>// When $o-&gt;get() is called, all of the above where clause will be included in the get query (with operators)</samp>
</pre>
</li>

	<li><strong>Associative array method:</strong>
<pre>
<var>$o </var><kbd>= new </kbd><var>Object</var><kbd>();
</kbd><var>$array </var><kbd>= array(</kbd><dfn>'name' </dfn><kbd>=&gt; </kbd><var>$name</var><kbd>, </kbd><dfn>'title' </dfn><kbd>=&gt; </kbd><var>$title</var><kbd>, </kbd><dfn>'status' </dfn><kbd>=&gt; </kbd><var>$status</var><kbd>);
</kbd><var>$o</var><kbd>-&gt;</kbd><var><u>where</u></var><kbd>(</kbd><var>$array</var><kbd>);
</kbd><samp>// When $o-&gt;get() is called, the array of where clauses will be included in the get query</samp>
</pre>

	<p>You can include your own operators using this method as well:</p>
<pre>
<var>$array </var><kbd>= array(</kbd><dfn>'name !=' </dfn><kbd>=&gt; </kbd><var>$name</var><kbd>, </kbd><dfn>'id &lt;' </dfn><kbd>=&gt; </kbd><var>$id</var><kbd>, </kbd><dfn>'date &gt;' </dfn><kbd>=&gt; </kbd><var>$date</var><kbd>);
</kbd><var>$o </var><kbd>= new </kbd><var>Object</var><kbd>();
</kbd><var>$o</var><kbd>&gt;</kbd><var><u>where</u></var><kbd>(</kbd><var>$array</var><kbd>);</kbd>
</pre>
	</li>

	<li><strong>Custom string:</strong>

	<p>You can write your own clauses manually:</p>
<pre>
<var>$where </var><kbd>= </kbd><dfn>"name='</dfn><var>Joe</var><dfn>' AND status='</dfn><var>boss</var><dfn>' OR status='</dfn><var>active</var><dfn>'"</dfn><kbd>;
</kbd><var>$o </var><kbd>= new </kbd><var>Object</var><kbd>();
</kbd><var>$o</var><kbd>-&gt;</kbd><var><u>where</u></var><kbd>(</kbd><var>$where</var><kbd>);</kbd>
</pre>
	</li>

</ol>


<h2 id="or_where">$object->or_where();</h2>

<p>This function is identical to the one above, except that multiple instances are joined by OR:</p>
<pre>
<var>$o </var><kbd>= new </kbd><var>Object</var><kbd>();
</kbd><var>$o</var><kbd>-&gt;</kbd><var><u>where</u></var><kbd>(</kbd><dfn>'name !='</dfn><kbd>, </kbd><var>$name</var><kbd>);
</kbd><var>$o</var><kbd>-&gt;</kbd><var><u>or_where</u></var><kbd>(</kbd><dfn>'id &gt;'</dfn><kbd>, </kbd><var>$id</var><kbd>);
</kbd><samp>// When $o-&gt;get() is called, all of the above where clause will be included in the get query separated by OR's</samp>
</pre>


<h2 id="where_in">$object->where_in();</h2>

<p>Generates a WHERE field IN ('item', 'item') SQL query joined with  AND if appropriate</p>
<pre>
<var>$o </var><kbd>= new </kbd><var>Object</var><kbd>();
</kbd><var>$names </var><kbd>= array(</kbd><dfn>'Frank'</dfn><kbd>, </kbd><dfn>'Todd'</dfn><kbd>, </kbd><dfn>'James'</dfn><kbd>);
</kbd><var>$o</var><kbd>-&gt;</kbd><var><u>where_in</u></var><kbd>(</kbd><dfn>'username'</dfn><kbd>, </kbd><var>$names</var><kbd>);
</kbd><samp>// When $o-&gt;get() is called, all records where the username is Frank, Todd, or James will be returned</samp>
</pre>

<h2 id="or_where_in">$object->or_where_in();</h2>
<p>Generates a WHERE field IN ('item', 'item') SQL query joined with OR if appropriate</p>
<pre>
<var>$o </var><kbd>= new </kbd><var>Object</var><kbd>();
</kbd><var>$firstnames </var><kbd>= array(</kbd><dfn>'Frank'</dfn><kbd>, </kbd><dfn>'Todd'</dfn><kbd>, </kbd><dfn>'James'</dfn><kbd>);
</kbd><var>$lastnames </var><kbd>= array(</kbd><dfn>'Smith'</dfn><kbd>, </kbd><dfn>'Jones'</dfn><kbd>);
</kbd><var>$o</var><kbd>-&gt;</kbd><var><u>where_in</u></var><kbd>(</kbd><dfn>'firstname'</dfn><kbd>, </kbd><var>$firstnames</var><kbd>);
</kbd><var>$o</var><kbd>-&gt;</kbd><var><u>or_where_in</u></var><kbd>(</kbd><dfn>'lastname'</dfn><kbd>, </kbd><var>$lastnames</var><kbd>);
</kbd><samp>// When $o-&gt;get() is called, all records where the firstname is Frank, Todd, or James, or all records where the lastname is Smith or Jones, will be returned</samp>
</pre>

<h2 id="where_not_in">$object->where_not_in();</h2>
<p>Generates a WHERE field NOT IN ('item', 'item') SQL query joined with AND if appropriate</p>
<pre>
<var>$o </var><kbd>= new </kbd><var>Object</var><kbd>();
</kbd><var>$names </var><kbd>= array(</kbd><dfn>'Frank'</dfn><kbd>, </kbd><dfn>'Todd'</dfn><kbd>, </kbd><dfn>'James'</dfn><kbd>);
</kbd><var>$o</var><kbd>-&gt;</kbd><var><u>where_not_in</u></var><kbd>(</kbd><dfn>'username'</dfn><kbd>, </kbd><var>$names</var><kbd>);
</kbd><samp>// When $o-&gt;get() is called, all records where the username is not Frank, Todd, or James will be returned</samp>
</pre>

<h2 id="or_where_not_in">$object->or_where_not_in();</h2>
<p>Generates a WHERE field NOT IN ('item', 'item') SQL query joined with OR if appropriate</p>
<pre>
<var>$o </var><kbd>= new </kbd><var>Object</var><kbd>();
</kbd><var>$firstnames </var><kbd>= array(</kbd><dfn>'Frank'</dfn><kbd>, </kbd><dfn>'Todd'</dfn><kbd>, </kbd><dfn>'James'</dfn><kbd>);
</kbd><var>$lastnames </var><kbd>= array(</kbd><dfn>'Smith'</dfn><kbd>, </kbd><dfn>'Jones'</dfn><kbd>);
</kbd><var>$o</var><kbd>-&gt;</kbd><var><u>where_not_in</u></var><kbd>(</kbd><dfn>'firstname'</dfn><kbd>, </kbd><var>$firstnames</var><kbd>);
</kbd><var>$o</var><kbd>-&gt;</kbd><var><u>or_where_not_in</u></var><kbd>(</kbd><dfn>'lastname'</dfn><kbd>, </kbd><var>$lastnames</var><kbd>);
</kbd><samp>// When $o-&gt;get() is called, all records where the firstname is not Frank, Todd, or James, or all records where the lastname is not Smith or Jones, will be returned</samp>
</pre>

<h2 id="like">$object->like();</h2>
<p>This function enables you to generate <strong>LIKE</strong> clauses, useful for doing searches.</p>
<p>For case-insensitive comparisons, see <a href="#ilike">ilike</a> below.</p>

<p class="note"><strong>Note:</strong> All values passed to this function are escaped automatically.</p>

<ol>
	<li><strong>Simple key/value method:</strong>
<pre>
<var>$o </var><kbd>= new </kbd><var>Object</var><kbd>();
</kbd><var>$o</var><kbd>-&gt;</kbd><var><u>like</u></var><kbd>(</kbd><dfn>'title'</dfn><kbd>, </kbd><dfn>'match'</dfn><kbd>);
</kbd><samp>// When $o-&gt;get() is called, all records with a title like match will be returned</samp>
</pre>

	<p>If you use multiple function calls they will be chained together with <var>AND</var> between them:</p>
<pre>
<var>$o </var><kbd>= new </kbd><var>Object</var><kbd>();
</kbd><var>$o</var><kbd>-&gt;</kbd><var><u>like</u></var><kbd>(</kbd><dfn>'title'</dfn><kbd>, </kbd><dfn>'match'</dfn><kbd>);
</kbd><var>$o</var><kbd>-&gt;</kbd><var><u>like</u></var><kbd>(</kbd><dfn>'body'</dfn><kbd>, </kbd><dfn>'match'</dfn><kbd>);
</kbd><samp>// When $o-&gt;get() is called, all records with a title like match and a body like match will be returned</samp>
</pre>

	<p>If you want to control where the wildcard (%) is placed, you can use an optional third argument. Your options are 'before', 'after' and 'both' (which is the default).</p>
<pre>
<var>$o </var><kbd>= new </kbd><var>Object</var><kbd>();
</kbd><var>$o</var><kbd>-&gt;</kbd><var><u>like</u></var><kbd>(</kbd><dfn>'title'</dfn><kbd>, </kbd><dfn>'match'</dfn><kbd>, </kbd><dfn>'after'</dfn><kbd>);
</kbd><samp>// When $o-&gt;get() is called, all records with a title starting with match will be returned</samp>
</pre>
	</li>

	<li><strong>Associative array method:</strong>
<pre>
<var>$array </var><kbd>= array(</kbd><dfn>'title' </dfn><kbd>=&gt; </kbd><var>$match</var><kbd>, </kbd><dfn>'page1' </dfn><kbd>=&gt; </kbd><var>$match</var><kbd>, </kbd><dfn>'page2' </dfn><kbd>=&gt; </kbd><var>$match</var><kbd>);
</kbd><var>$o </var><kbd>= new </kbd><var>Object</var><kbd>();
</kbd><var>$o</var><kbd>-&gt;</kbd><var><u>like</u></var><kbd>(</kbd><var>$array</var><kbd>);
</kbd><samp>// When $o-&gt;get() is called, all records with the title, page1, and page2 like the specified matches will be returned</samp>
</pre>
	</li>
</ol>


<h2 id="or_like">$object->or_like();</h2>
<p>This function is identical to the one above, except that multiple instances are joined by OR:</p>
<pre>
<var>$o </var><kbd>= new </kbd><var>Object</var><kbd>();
</kbd><var>$o</var><kbd>-&gt;</kbd><var><u>like</u></var><kbd>(</kbd><dfn>'title'</dfn><kbd>, </kbd><dfn>'match'</dfn><kbd>);
</kbd><var>$o</var><kbd>-&gt;</kbd><var><u>or_like</u></var><kbd>(</kbd><dfn>'body'</dfn><kbd>, </kbd><var>$match</var><kbd>);
</kbd><samp>// When $o-&gt;get() is called, all records with a title like match or a body like match will be returned</samp>
</pre>



<h2 id="not_like">$object->not_like();</h2>
<p>This function is identical to <strong>like()</strong>, except that it generates NOT LIKE statements:</p>
<pre>
<var>$o </var><kbd>= new </kbd><var>Object</var><kbd>();
</kbd><var>$o</var><kbd>-&gt;</kbd><var><u>not_like</u></var><kbd>(</kbd><dfn>'title'</dfn><kbd>, </kbd><dfn>'match'</dfn><kbd>);
</kbd><samp>// When $o-&gt;get() is called, all records with a title not like match will be returned</samp>
</pre>


<h2 id="or_not_like">$object->or_not_like();</h2>
<p>This function is identical to <strong>not_like()</strong>, except that multiple instances are joined by OR:</p>
<pre>
<var>$o </var><kbd>= new </kbd><var>Object</var><kbd>();
</kbd><var>$o</var><kbd>-&gt;</kbd><var><u>like</u></var><kbd>(</kbd><dfn>'title'</dfn><kbd>, </kbd><dfn>'match'</dfn><kbd>);
</kbd><var>$o</var><kbd>-&gt;</kbd><var><u>or_not_like</u></var><kbd>(</kbd><dfn>'body'</dfn><kbd>, </kbd><dfn>'match'</dfn><kbd>);
</kbd><samp>// When $o-&gt;get() is called, all records with a title like match or a body not like match will be returned</samp>
</pre>


<h2 id="ilike">$object->ilike();</h2>
<p>The ilike methods have exactly the same format as the <a href="#like">like</a> methods.  However, they convert both the query and the column to upper case first, to ensure case-insensitive matching.  This method is better than writing your own, because it can protect identifiers and the string properly.</p>
<p>Also available as <var><u>or_ilike</u></var>, <var><u>not_ilike</u></var>, and <var><u>or_not_ilike</u></var>.</p>



<p>&nbsp;</p>

<h1 id="Query.Grouping">Query Grouping</h1>
<p>You can create more advanced queries by grouping your clauses.  This allows you to specify construct such as <tt>(a OR b) AND (c OR NOT d)</tt>.</p>
<p class="note"><strong><em>Note:</em></strong> Every <var><u>group_start</u></var> must be balanced by exactly one <var><u>group_end</u></var>.</p>

<h2 id="group_start">$object->group_start()</h2>
<p>Starts a group.  Every statement generated until <var><u>group_end</u></var> will be joined by an AND to the rest of the query.  Groups can be nested.</p>
<p>Example below.</p>

<h2 id="OR_group_start">$object->or_group_start</h2>
<p>Every statement generated until <var><u>group_end</u></var> will be joined by an OR to the rest of the query.</p>

<h2 id="not_group_start">$object->not_group_start</h2>
<p>Every statement generated until <var><u>group_end</u></var> will be joined by an AND NOT to the rest of the query.</p>

<h2 id="or_not_group_start">$object->or_not_group_start</h2>
<p>Every statement generated until <var><u>group_end</u></var> will be joined by an OR NOT to the rest of the query.</p>

<h2 id="group_end">$object->group_end</h2>
<p>Ends the most recently started group.</p>

<h3>Grouping Example</h3>
<pre>
<var>$o </var><kbd>= new </kbd><var>Object</var><kbd>();

</kbd><samp>// Returns all objects where a, or where b AND c
// SQL: a OR b AND c
</samp><var>$o</var><kbd>-&gt;</kbd><var><u>where</u></var><kbd>(</kbd><dfn>'a'</dfn><kbd>, </kbd><var><dfn>TRUE</dfn></var><kbd>)-&gt;</kbd><var><u>or_where</u></var><kbd>(</kbd><dfn>'b'</dfn><kbd>, </kbd><var><dfn>TRUE</dfn></var><kbd>)-&gt;</kbd><var><u>where</u></var><kbd>(</kbd><dfn>'c'</dfn><kbd>, </kbd><var><dfn>TRUE</dfn></var><kbd>)-&gt;</kbd><var><u>get</u></var><kbd>();

</kbd><samp>// Returns all objects where a, and where b or c
// SQL: a AND (b OR c)
</samp><var>$o</var><kbd>-&gt;</kbd><var><u>where</u></var><kbd>(</kbd><dfn>'a'</dfn><kbd>, </kbd><var><dfn>TRUE</dfn></var><kbd>)-&gt;</kbd><var><u>group_start</u></var><kbd>()-&gt;</kbd><var><u>where</u></var><kbd>(</kbd><dfn>'b'</dfn><kbd>, </kbd><var><dfn>TRUE</dfn></var><kbd>)-&gt;</kbd><var><u>or_where</u></var><kbd>(</kbd><dfn>'c'</dfn><kbd>, </kbd><var><dfn>TRUE</dfn></var><kbd>)-&gt;</kbd><var><u>group_end</u></var><kbd>()-&gt;</kbd><var><u>get</u></var><kbd>();

</kbd><samp>// Returns all objects where a AND b, or where c
// SQL: (a AND b) OR c
</samp><var>$o</var><kbd>-&gt;</kbd><var><u>group_start</u></var><kbd>()-&gt;</kbd><var><u>where</u></var><kbd>(</kbd><dfn>'a'</dfn><kbd>, </kbd><var><dfn>TRUE</dfn></var><kbd>)-&gt;</kbd><var><u>where</u></var><kbd>(</kbd><dfn>'b'</dfn><kbd>, </kbd><var><dfn>TRUE</dfn></var><kbd>)-&gt;</kbd><var><u>group_end</u></var><kbd>()-&gt;</kbd><var><u>or_where</u></var><kbd>(</kbd><dfn>'c'</dfn><kbd>, </kbd><var><dfn>TRUE</dfn></var><kbd>)-&gt;</kbd><var><u>get</u></var><kbd>();</kbd>
</pre>

<h3>Nested Grouping Example</h3>
<pre>
<samp>// Generates:
// (a AND (b OR c)) AND d
</samp><var>$o</var><kbd>-&gt;</kbd><var><u>group_start</u></var><kbd>()
    -&gt;</kbd><var><u>where</u></var><kbd>(</kbd><dfn>'a'</dfn><kbd>, </kbd><var><dfn>TRUE</dfn></var><kbd>)
    -&gt;</kbd><var><u>group_start</u></var><kbd>()
        -&gt;</kbd><var><u>where</u></var><kbd>(</kbd><dfn>'b'</dfn><kbd>, </kbd><var><dfn>TRUE</dfn></var><kbd>)
        -&gt;</kbd><var><u>or_where</u></var><kbd>(</kbd><dfn>'c'</dfn><kbd>, </kbd><var><dfn>TRUE</dfn></var><kbd>)
    -&gt;</kbd><var><u>group_end</u></var><kbd>()
-&gt;</kbd><var><u>group_end</u></var><kbd>()
-&gt;</kbd><var><u>where</u></var><kbd>(</kbd><dfn>'d'</dfn><kbd>, </kbd><var><dfn>TRUE</dfn></var><kbd>)-&gt;</kbd><var><u>get</u></var><kbd>();
</kbd>
</pre>


<p>&nbsp;</p>

<h1 id="Other.Features">Other Features</h1>
<p>Other features related to generating your SQL query.  (You can apply many of these methods to related objects and join tables in the next section, <a href="getadvanced.html#Get.Advanced">Get (Advanced)</a>.)</p>

<h2 id="group_by">$object->group_by();</h2>
<p>Permits you to write the GROUP BY portion of your query:</p>
<pre>
<var>$o </var><kbd>= new </kbd><var>Object</var><kbd>();
</kbd><var>$o</var><kbd>-&gt;</kbd><var><u>group_by</u></var><kbd>(</kbd><dfn>'title'</dfn><kbd>);
</kbd><samp>// When $o-&gt;get() is called, all returned records will be grouped by title</samp>
</pre>

<p>You can also pass an array of multiple values as well:</p>
<pre>
<var>$o </var><kbd>= new </kbd><var>Object</var><kbd>();
</kbd><var>$o</var><kbd>-&gt;</kbd><var><u>group_by</u></var><kbd>(</kbd><dfn>'title'</dfn><kbd>, </kbd><dfn>'date'</dfn><kbd>);
</kbd><samp>// When $o-&gt;get() is called, all returned records will be grouped by title and then date</samp>
</pre>

<h2 id="having">$object->having();</h2>
<p>Permits you to write the HAVING portion of your query. There are 2 possible syntaxe, 1 argument or 2:</p>
<pre>
<var>$o </var><kbd>= new </kbd><var>Object</var><kbd>();
</kbd><var>$o</var><kbd>-&gt;</kbd><var><u>having</u></var><kbd>(</kbd><dfn>'user_id = 45'</dfn><kbd>);

</kbd><samp>// When $o-&gt;get() is called, all records having a user_id of 45 will be returned

</samp><var>$o</var><kbd>-&gt;</kbd><var><u>having</u></var><kbd>(</kbd><dfn>'user_id'</dfn><kbd>,  </kbd><var>45</var><kbd>);
</kbd><samp>// As above, when $o-&gt;get() is called, all records having a user_id of 45 will be returned</samp>
</pre>

<p>You can also pass an array of multiple values as well:</p>
<pre>
<var>$o </var><kbd>= new </kbd><var>Object</var><kbd>();
</kbd><var>$o</var><kbd>-&gt;</kbd><var><u>having</u></var><kbd>(array(</kbd><dfn>'title =' </dfn><kbd>=&gt; </kbd><dfn>'My Title'</dfn><kbd>, </kbd><dfn>'id &lt;' </dfn><kbd>=&gt; </kbd><var>$id</var><kbd>));
</kbd><samp>// When $o-&gt;get() is called, all records having a title of My Title and an id less than 45 will be returned</samp>
</pre>

<p>If you are using a database that CodeIgniter escapes queries for, you can prevent escaping content by passing an optional third argument, and setting it to FALSE.</p>
<pre>
<var>$o </var><kbd>= new </kbd><var>Object</var><kbd>();
</kbd><var>$o</var><kbd>-&gt;</kbd><var><u>having</u></var><kbd>(</kbd><dfn>'user_id'</dfn><kbd>,  </kbd><var>45</var><kbd>, </kbd><var><dfn>FALSE</dfn></var><kbd>);</kbd>
</pre>


<h2 id="or_having">$object->or_having();</h2>
<p>Identical to having(), only separates multiple clauses with &quot;OR&quot;.</p>

<h2 id="order_by">$object->order_by();</h2>
<p>Lets you set an ORDER BY clause. The first parameter contains the name of the column you would like to order by.
The second parameter lets you set the direction of the result.  Options are <kbd>asc</kbd> or <kbd>desc</kbd>, or <kbd>random</kbd>. </p>
<pre>
<var>$o </var><kbd>= new </kbd><var>Object</var><kbd>();
</kbd><var>$o</var><kbd>-&gt;</kbd><var><u>order_by</u></var><kbd>(</kbd><dfn>"title"</dfn><kbd>, </kbd><dfn>"desc"</dfn><kbd>);
</kbd><samp>// When $o-&gt;get() is called, all returned records will be ordered by title descending</samp>
</pre>

<p>You can also pass your own string in the first parameter:</p>
<pre>
<var>$o </var><kbd>= new </kbd><var>Object</var><kbd>();
</kbd><var>$o</var><kbd>-&gt;</kbd><var><u>order_by</u></var><kbd>(</kbd><dfn>'title desc, name asc'</dfn><kbd>);
</kbd><samp>// When $o-&gt;get() is called, all returned records will be ordered by title descending, then name ascending</samp>
</pre>

<p>Or multiple function calls can be made if you need multiple fields.</p>
<pre>
<var>$o </var><kbd>= new </kbd><var>Object</var><kbd>();
</kbd><var>$o</var><kbd>-&gt;</kbd><var><u>order_by</u></var><kbd>(</kbd><dfn>"title"</dfn><kbd>, </kbd><dfn>"desc"</dfn><kbd>);
</kbd><var>$o</var><kbd>-&gt;</kbd><var><u>order_by</u></var><kbd>(</kbd><dfn>"name"</dfn><kbd>, </kbd><dfn>"asc"</dfn><kbd>);
</kbd><samp>// When $o-&gt;get() is called, all returned records will be ordered by title descending, then name ascending</samp>
</pre>
<p class="important">Note: random ordering is not currently supported in Oracle or MSSQL drivers. These will default to 'ASC'.</p>

<h2 id="Default.Order.By">Default Order By</h2>
<p>You can specify a default order to your classes, by setting the variable <var><i>$default_order_by</i></var>.</p>
<pre>
<var></span><kbd>class </var><var>Task </var><span style="color: #007700">extends </kbd><var><u>DataMapper</u> </var><kbd>{
    ...
    </kbd><samp>// Default to sorting tasks with overdue tasks at the top, then priority, then title.
    </samp><kbd>var </kbd><var><i>$default_order_by</i> </var><kbd>= array(</kbd><dfn>'overdue' </dfn><kbd>=&gt; </kbd><dfn>'desc'</dfn><kbd>, </kbd><dfn>'priority' </dfn><kbd>=&gt; </kbd><dfn>'desc'</dfn><kbd>, </kbd><dfn>'title'</dfn><kbd>);
    ...
}</kbd>
</pre>

<p>Now whenever you call, for example, <var>$task</var><kbd>-&gt;</kbd><var><u>get</u></var><kbd>()</kbd> or <var>$user</var><kbd>-&gt;</kbd><var>tasks</var><kbd>-&gt;</kbd><var><u>get</u></var><kbd>()</kbd>, the results will automatically be sorted.</p>
<div class="note">
	<p>To prevent SQL errors, automatic sorting is disabled in these cases:</p>
	<ul>
		<li>If no default sort order has been specified.</li>
		<li>If you specify your own sort order, using a <var><u>order_by</u></var> method.</li>
		<li>The query does not have <strong>*</strong> or <strong>table.*</strong> selected.  This would only be when you have overridden the default selection.</li>
	</ul>
</div>


<h2 id="limit">$object->limit();</h2>
<p>Lets you limit the number of rows you would like returned by the query:</p>
<pre>
<var>$o </var><kbd>= new </kbd><var>Object</var><kbd>();
</kbd><var>$o</var><kbd>-&gt;</kbd><var><u>limit</u></var><kbd>(</kbd><var>10</var><kbd>);
</kbd><samp>// When $o-&gt;get() is called, the number of records returned will be limited to 10</samp>
</pre>


<p>The second parameter lets you set a result offset.</p>
<pre>
<var>$o </var><kbd>= new </kbd><var>Object</var><kbd>();
</kbd><var>$o</var><kbd>-&gt;</kbd><var><u>limit</u></var><kbd>(</kbd><var>10</var><kbd>, </kbd><var>20</var><kbd>);
</kbd><samp>// When $o-&gt;get() is called, the number of records returned will be limited to 10, starting from record 20</samp>
</pre>





<p>&nbsp;</p>

<h1 id="Method.Chaining"> Method Chaining</h1>

<p>Method chaining allows you to simplify your syntax by connecting multiple functions.  Consider this example:</p>
<pre>
<var>$o </var><kbd>= new </kbd><var>Object</var><kbd>();
</kbd><var>$o</var><kbd>-&gt;</kbd><var><u>where</u></var><kbd>(</kbd><dfn>'id'</dfn><kbd>, </kbd><var>$id</var><kbd>)-&gt;</kbd><var><u>limit</u></var><kbd>(</kbd><var>10</var><kbd>, </kbd><var>20</var><kbd>)-&gt;</kbd><var><u>get</u></var><kbd>();</kbd>
</pre>

<p>The alternate of the above without method chaining would be:</p>
<pre>
<var>$o </var><kbd>= new </kbd><var>Object</var><kbd>();
</kbd><var>$o</var><kbd>-&gt;</kbd><var><u>where</u></var><kbd>(</kbd><dfn>'id'</dfn><kbd>, </kbd><var>$id</var><kbd>);
</kbd><var>$o</var><kbd>-&gt;</kbd><var><u>limit</u></var><kbd>(</kbd><var>10</var><kbd>, </kbd><var>20</var><kbd>);
</kbd><var>$o</var><kbd>-&gt;</kbd><var><u>get</u></var><kbd>();</kbd>
</pre>






<p>&nbsp;</p>

<h1 id="Active.Record.Caching"> Active Record Caching</h1>
<p>Since DataMapper uses Active Record for all its queries, it makes sense you should be able to access the Active Record caching methods. While not &quot;true&quot; caching, Active Record enables you to save (or &quot;cache&quot;) certain parts of your queries for reuse later. Normally, when an Active Record call is completed, all stored information is reset for the next call. With caching, you can prevent this reset, and reuse information easily.</p>

<p>Cached calls are cumulative.  If you make 2 cached select() calls, and then 2 uncached select() calls, this will result in 4 select() calls. There are three Caching functions available:</p>
<h2 id="start_cache">$object->start_cache()</h2>
<p>This function must be called to begin caching. All Active Record queries of the correct type (see below for supported queries) are stored for later use.</p>
<h2 id="stop_cache">$object->stop_cache()</h2>
<p>This function can be called to stop caching.</p>
<h2 id="flush_cache">$object->flush_cache()</h2>
<p>This function deletes all items from the Active Record cache.</p>
<p>Here's a usage example:</p>
<pre>
<var>$o </var><kbd>= new </kbd><var>Object</var><kbd>();
</kbd><var>$o</var><kbd>-&gt;</kbd><var><u>start_cache</u></var><kbd>();
</kbd><var>$o</var><kbd>-&gt;</kbd><var><u>select</u></var><kbd>(</kbd><dfn>'field1'</dfn><kbd>);
</kbd><var>$o</var><kbd>-&gt;</kbd><var><u>stop_cache</u></var><kbd>();
</kbd><var>$o</var><kbd>-&gt;</kbd><var><u>get</u></var><kbd>();
</kbd><samp>// The $o object is populated with all records from its corresponding table, but with only the 'field1' field being populated

</samp><var>$o</var><kbd>-&gt;</kbd><var><u>select</u></var><kbd>(</kbd><dfn>'field2'</dfn><kbd>);
</kbd><var>$o</var><kbd>-&gt;</kbd><var><u>get</u></var><kbd>();
</kbd><samp>// The $o object is populated with all records from its corresponding table, but with both the 'field1' and 'field2' fields being populated

</samp><var>$o</var><kbd>-&gt;</kbd><var><u>flush_cache</u></var><kbd>();

</kbd><var>$o</var><kbd>-&gt;</kbd><var><u>select</u></var><kbd>(</kbd><dfn>'field2'</dfn><kbd>);
</kbd><var>$o</var><kbd>-&gt;</kbd><var><u>get</u></var><kbd>();
</kbd><samp>// The $o object is populated with all records from its corresponding table, but with only the 'field2' field being populated</samp>
</pre>

<p class="note"><strong><em>Note:</em></strong> The following fields can be cached: ‘select’, ‘from’, ‘join’, ‘where’, ‘like’, ‘group_by’, ‘having’, ‘order_by’, ‘set’</p>

</div>
<!-- END CONTENT -->


<div id="footer">
<p>
<span id="footer_previous">Previous Topic:&nbsp;&nbsp;<a href=""></a>
&nbsp;&nbsp;&nbsp;&middot;&nbsp;&nbsp;</span>
<a href="#top">Top of Page</a>&nbsp;&nbsp;&nbsp;&middot;&nbsp;&nbsp;
<a href="../index.html">User Guide Home</a>
<span id="footer_next">&nbsp;&nbsp;&nbsp;&middot;&nbsp;&nbsp;
Next Topic:&nbsp;&nbsp;<a href=""></a></span>
</p>
<div id="copyrights">
<p><a href="/">Datamapper ORM</a> &nbsp;&middot;&nbsp; Copyright &copy; 2010-2011 &nbsp;&middot;&nbsp; Harro "WanWizard" Verton</p>
<p><a href="license.html">Other License Information</a></p>
</div>
</div>

<script type="text/javascript" src="../js/mootools.js"></script>
<script type="text/javascript" src="../js/menu.js"></script>
<script type="text/javascript">
<!--
	window.addEvent('domready', function() {

		// Create Menu
		var menu = new Menu({
			basepath: '../',
			pagespath: ''
		});

	});
//-->
</script>
</body>
</html>
